# 导入库
import pandas as pd
import copy
import datetime as dt

# 打开文件读数据
df = pd.read_excel('modeling.xlsx')


# 数据清洗：从原表中将包含#ERROR列的公司股票数据删除
def get_index(lst=None, item=''):
    return [index for (index, value) in enumerate(lst) if value.find(item) >= 0]


errCols = get_index(df.columns.values, '#ERROR')  # 标题为#ERROR的列
errComs = [(errCol - 1) // 8 for errCol in errCols]  # 有空值的公司序号

delColsIdx = []  # 有空值的公司参数列序号
for i in errComs:
    delColsIdx += [i * 8 + j for j in range(1, 9)]
delCols = [df.columns.values[i] for i in delColsIdx]  # 有空值的公司参数列索引列表
df.drop(delCols, axis=1, inplace=True)  # 删除有空值的公司的所有参数列

# 利用enumerate对原表最后一行进行遍历，将含有空值的列放入cols中
cols = [x for i, x in enumerate(df.columns) if df.iloc[-1, i] != df.iloc[-1, i]]
# 查找最后一行各空值所在列上方的最接近的非空值，将其值复制到最后一行
for x in cols:
    for j in range(len(df.index) - 2, 0, -1):
        if df[x][j] == df[x][j]:
            df.loc[len(df.index) - 1, x] = df[x][j]
            break

# 从原表中取得股票参数名称列表
terms = []
terms = copy.copy(df.columns.values[1:9:])
for i in range(0, len(terms)):
    terms[i] = terms[i][terms[i].find(' - ') + 3:]

# 从原表中取得全部公司名称列表
companies = []
companies = copy.copy(df.columns.values[1::8])

for i in range(0, len(companies)):
    companies[i] = companies[i][:companies[i].find(' -')]

# 将原表中的美元换算为加元
# 转换函数
conv = lambda x: x * 1.33 if (isinstance(x, int) or isinstance(x, float)) else x
# 按照每个公司的数据依次换算
for i in range(len(companies)):
    if 'U$' in list(df.iloc[0, i * 8 + 1:i * 8 + 8]):  # 如果CURRENCY是‘U$’
        df.iloc[0, i * 8 + 1:i * 8 + 8] = 'C$'  # 将CURRENCY标题改为‘C$’
        for j in range(1, 9):  # 换算每个单元格数据
            for k in range(1, len(df.index)):
                df.iloc[k, i * 8 + j] = conv(df.iloc[k, i * 8 + j])
# 为什么DateFrame和Series的applymap和map函数都不行？？？ 如果可以，则运算效率可以提高很多
#        for j in range(1,9):
#            df.iloc[1:,i*8+j].map(conv)
#        df.iloc[1:,i*8+1:i*8+8].applymap(conv)

# 创建新DataFrame，存储选股因子
# EV : Enterprise Value、公司价值或企业价值
# PER : Price-Earnings Ratio、PE、市盈率或本益比
# PEG : Price to Earnings Growth、市盈率增长比率
# EPS : Earnings Per Share、每股收益或每股盈利
# DPR : Dividend Payout Ratio、股息支付率
# ROE : Return on Equity、净资产收益率、权益报酬率
# DER : Debt to Equity Ratio债务权益比率、债务对股东权益比率
factors = ['EV', 'PER', 'PEG', 'EPS', 'DPR', 'ROE', 'DER']  # 选股因子名称列表
df2 = pd.DataFrame(columns=['company'] + factors)

# 用原表中最后一行及一年前的数据产生新表中的数据
# TECHNIPFMC，UNDER ARMOUR 'C'公司第2，3列数据全部缺失，已删除
for i in range(len(companies)):
    if df.iloc[-1, i * 8 + 3] != 0 and df.iloc[-1, i * 8 + 3] != df.iloc[61, i * 8 + 3]:
        # 取公司名称
        company = companies[i]
        # 取公司股票8个参数值
        # EV:Enterprise Value, PER:Price-Earnings Ratio, EPS:Earnings Per Share, DPS:DIV_PER_SHR
        # NOS:NUMBER_OF_SHARES, ROE:Return on Equity, TL:TOTAL_LIABILITIES, TSE:TOTAL_SHAREHOLDERS_EQUITY
        EV, PER, EPS, DPS, NOS, ROE, TL, TSE = (df.iloc[-1, i * 8 + j] for j in range(1, 9))
        EPS_T = EPS
        EPS_T_1 = df.iloc[-2, i * 8 + 3]
        # 计算股票的各个factors
        if EPS_T != EPS_T_1:
            PEG = PER * EPS_T_1 / ((EPS_T - EPS_T_1) * 100)
        DP = DPS / PER  # DP:DividendPayout
        DER = TL / TSE  # DER:Debt Equity Ratio
        df2 = df2._append(pd.DataFrame({'company': [company], factors[0]: [EV], factors[1]: [PER],
                                        factors[2]: [PEG], factors[3]: [EPS],
                                        factors[4]: [DP], factors[5]: [ROE],
                                        factors[6]: [DER]}), ignore_index=True)

# 求出全部股票的所有factors平均值
df2.mean(numeric_only=True)

# 根据factor列表中各因子排序
df_c1 = df2.sort_values(by=factors[0], ascending=True)  # 根据 EV 按升序排序
df_c2 = df2.sort_values(by=factors[1], ascending=True)  # 根据 PER 按升序排序
df_c3 = df2.sort_values(by=factors[2], ascending=True)  # 根据 PEG 按升序排序
df_c4 = df2.sort_values(by=factors[3], ascending=False)  # 根据 EPS 按降序排序
df_c5 = df2.sort_values(by=factors[4], ascending=False)  # 根据 DP 按降序排序
df_c6 = df2.sort_values(by=factors[5], ascending=False)  # 根据 ROE 按降序排序
df_c7 = df2.sort_values(by=factors[6], ascending=True)  # 根据 DER 按升序排序

# 求各次排序中前若干项股票列表
top_num = 400
stocks1 = list(df_c1.head(top_num)['company'])
stocks2 = list(df_c2.head(top_num)['company'])
stocks3 = list(df_c3.head(top_num)['company'])
stocks4 = list(df_c4.head(top_num)['company'])
stocks5 = list(df_c5.head(top_num)['company'])
stocks6 = list(df_c6.head(top_num)['company'])
stocks7 = list(df_c7.head(top_num)['company'])
# 求各次排序前若干只股票的交集。
good_stocks = [s for s in stocks1 if
               (s in stocks2 and s in stocks3 and s in stocks4 and s in stocks5 and s in stocks6 and s in stocks7)]

# 将筛选出的股票写进一个DataFrame中
df_goodStocks = df2[df2['company'].isin(good_stocks)]
# 显示筛选出的股票及factors值
print(df_goodStocks)
# 将DataFrame写入一个Excel文件
df_goodStocks.to_excel('goodStocks.xlsx')
